﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Collections.ObjectModel;
using System.Data.Linq.Mapping;

namespace DentistApplication
{
    public static class DatabaseHandler
    {
        private static dbDataContext dbase;

        public static bool connectToDatabase()
        {
            try
            {
                dbase = new dbDataContext();
                //dbase = new dbDataContext(@"Data Source=LILY\SQLEXPRESS;Initial Catalog=dentAppBase;Integrated Security=True");
                return true;

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return false;
            }
        }

        #region PATIENTS
        // PATIENTS //////////////////////////////////////////////////////////////////////////////////

        public static bool addPatientToDatabase(Patient p)
        {
            //check in base if the patient isn't already added
            //add new patient to database
            dbase.Patients.InsertOnSubmit(p);
            return submitChanges();
        }

        public static List<Patient> searchForPatients(string name, string lastname, string pesel)
        {
            //TO DO!!!!
            var n = from p in dbase.Patients
                    where p.firstname.Contains(name) && p.lastname.Contains(lastname) && p.pesel.Contains(pesel)
                    select p;
            List<Patient> lp = n.ToList();
            return lp;
        }

        internal static List<Patient> getAllPatients()
        {
            var n = from p in dbase.Patients
                    select p;

            return n.ToList();
        }

        public static List<string> getFieldsInPatientTable()
        {
            var cNames = dbase.ColumnNames<Patient>().Where
                (n => n.Member.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), false)
                .FirstOrDefault() != null).Select(n => n.Name);

            List<string> columnNames = cNames.ToList();
            return columnNames;
        }

        public static Patient getPatient(int patientId)
        {
            var n = from Patient p in dbase.Patients
                    where p.id == patientId
                    select p;

            return n.ToList()[0];
        }

        public static bool addNoteToPatient(int patientID, string note)
        {
            Patient p = getPatient(patientID);
            p.notes = note;
            return submitChanges();
        }

        #endregion

        #region PERSONEL
// PERSONEL //////////////////////////////////////////////////////////////////////////////////
        
        public static IQueryable<String> getDentists()
        {
            var n = from Personel p in dbase.Personels
                    where p.Role.name.Contains("Doktor")
                    select (p.firstname + ' ' + p.lastname);
            return n;
        }

        public static string readPassword(string login)
        {

            try
            {
                var n = from p in dbase.Personels
                         where p.login.Contains(login)
                         select p.password;
                return n.Single();
                
            }
            catch
            {
                return "";
            }
        }

        public static Personel getUser(string login)
        {
            Personel User = new Personel();
            try
            {
                var n = (from p in dbase.Personels
                         where p.login.Contains(login)
                         select p).Single();
                User = n;
            }
            catch
            {               
            }
            return User;
        }



        #endregion

        #region VISIT
        // VISIT //////////////////////////////////////////////////////////////////////////////////

        public static bool saveVisit(Visit v, List<Service> services, List<Recognition> recognitions) 
        {
            foreach (Service s in services)
            {
                v.Services.Add(s);
            }
            foreach (Recognition r in recognitions)
            {
                v.Recognitions.Add(r);
            }
            dbase.Visits.InsertOnSubmit(v);
            return submitChanges();
        }

        public static bool addVisitToDatabase(Visit v)
        {
           
            return submitChanges();
        }

        public static List<Visit> getPatientsVisits(int patiendID)
        {
            var n = from s in dbase.Visits
                    where s.id_patient == patiendID
                    select s;

            return n.ToList();
        }

        public static DateTime getPatientLastVisit(Patient p)
        {
            var n = (from r in dbase.Visits
                     where r.id_patient.Equals(p.id)
                     orderby r.id descending
                     select r.date).First();
            return n;
        }

        #endregion
        
        #region SERVICE
        // SERVICE //////////////////////////////////////////////////////////////////////////////////

        public static bool addServiceToDatabase(Service s)
        {
            dbase.Services.InsertOnSubmit(s);
            return submitChanges();
        }

        public static List<Service> getPatientsAllServices(int patiendID)
        {
            var n = from s in dbase.Services
                    where s.Visit.id_patient == patiendID
                    select s;

            return n.ToList();
        }

        public static List<Service> getPatientsServices(int patiendID, int tooth)
        {
            var n = from s in dbase.Services
                    where s.Visit.id_patient == patiendID && s.tooth == tooth
                    select s;

            return n.ToList();
        }

        public static List<Service> getPatientsServicesFromVisit(int patiendID, int visitID)
        {
            var n = from s in dbase.Services
                    where s.Visit.id_patient == patiendID && s.id_visit == visitID
                    select s;

            return n.ToList();
        }

        #endregion

        #region SERVICE_TYPES
        // SERVICE TYPES //////////////////////////////////////////////////////////////////////////////////

        public static IQueryable<String> getAllServiceTypes()
        {
            var n = from s in dbase.Service_types
                    select s.type_name;

            return n;
        }

        public static List<int> getAllServiceTypesIDs()
        {
            var n = from s in dbase.Service_types
                    select s.id;

            return n.ToList();
        }

        public static IQueryable<String> getGroupServiceTypes(string Group)
        {
            var n = from s in dbase.Service_types
                    where s.service_group == Group
                    select s.type_name;
            return n;
        }
        public static List<int> getGroupServiceTypesIDs(string Group)
        {
            var n = from s in dbase.Service_types
                    where s.service_group == Group
                    select s.id;
            return n.ToList();
        }

        public static string getServiceTypeName(int ServiceTypeId)
        {
            var n = from s in dbase.Service_types
                    where s.id == ServiceTypeId
                    select s.type_name;

            if (n != null)
                return n.ToList()[0];
            return "unknown";
        }

        #endregion

        #region Recognition
        // Recognition //////////////////////////////////////////////////////////////////////////////////

        public static bool addToothToDatabase(Recognition t)
        {
            dbase.Recognitions.InsertOnSubmit(t);
            return submitChanges();
        }
        //public static List<Tooth> getTooths(int patiendID)
        //{
        //    var n = from s in dbase.Tooths
        //            where s == patiendID
        //            select s;

        //    return n.ToList();
        //}

        public static List<Recognition> getPatientRecognitions(int patiendID)
        {
            var n = from r in dbase.Recognitions
                    where r.Visit.id_patient == patiendID
                    select r;
            return n.ToList();
        }

        public static Recognition getServiceTypes(int ServiceTypeID)
        {
            var n = (from s in dbase.Recognitions
                     where s.id == ServiceTypeID
                     select s).First();

            return (Recognition)n;
        }




        #endregion

        #region RECOGNITION TYPES
        // RECOGNITION TYPES //////////////////////////////////////////////////////////////////////////////////

        public static Recognition_type getRecognitionType(int recogniotionTypeId)
        {
            var n = (from r in dbase.Recognition_types
                    where r.id == recogniotionTypeId
                    select r).First();

            return (Recognition_type)n;
        }

        public static List<Recognition_type> getAllRecognitionTypes()
        {
            var n = from r in dbase.Recognition_types
                     select r;

            return n.ToList();
        }


 
        #endregion

        #region TIMETABLE
        // TIMETABLE //////////////////////////////////////////////////////////////////////////////////
        

        public static bool addAppointmentToDatabase(Timetable t)
        {
            dbase.Timetables.InsertOnSubmit(t);
            return submitChanges();
        }
        
        public static List<Timetable> getAllAppointments()
        {
            var n = from Timetable in dbase.Timetables
                    select Timetable;

            return n.ToList();
        }

        public static List<Timetable> getPAtientAppointments(int patientId)
        {
            var n = from Timetable t in dbase.Timetables
                    where t.patient_id == patientId
                    select t;

            return n.ToList();
        }

        #endregion
               


        // 2 ALL //////////////////////////////////////////////////////////////////////////////////


        private static bool submitChanges()
        {
            try
            {
                dbase.SubmitChanges();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
        }

        
    }
        public static class LinqExtensions
        {
            public static ReadOnlyCollection<MetaDataMember> ColumnNames<TEntity>(this DataContext source)
            {
                return source.Mapping.MappingSource.GetModel(typeof(DataContext)).GetMetaType(typeof(TEntity)).DataMembers;
            }
        }
}
